from zoneinfo import ZoneInfo
from openpyxl import load_workbook
import pandas as pd


class TimeConverter:
    """
    This class represents a time converter, which can convert the local destination time to Beijing time.
    """

    def __init__(self, input_file, output_file):
        """
        Initializes input and output files, and defines time zones for destinations.
        """
        self.timezones = {'LAX': 'America/Los_Angeles', 'ORD': 'America/Chicago', 'JFK': 'America/New_York'}
        self.data = pd.read_excel(input_file)
        self.data['当地到达时间'] = pd.to_datetime(self.data['当地到达时间'])
        self.output_file = output_file

    def convert_time(self, row):
        """
        Converts local time of a single row to Beijing time.
        """
        if row['目的地'] in self.timezones:
            local_time = row['当地到达时间'].replace(tzinfo=ZoneInfo(self.timezones[row['目的地']]))
            beijing_time = local_time.astimezone(ZoneInfo('Asia/Shanghai'))
            return beijing_time.replace(tzinfo=None)
        else:
            return "目的地代码有误，请人工审核！"

    def apply_conversion(self):
        """
        Applies the time conversion function to all rows of data.
        """
        self.data['北京时间'] = self.data.apply(self.convert_time, axis=1)

    def save_to_file(self):
        """
        Saves the converted data to an Excel file and adjusts the column width.
        """
        self.data.to_excel(self.output_file, index=False)

        book = load_workbook(self.output_file)
        sheet = book.active

        widths = {'A': 30, 'C': 30, 'D': 30}

        for column, width in widths.items():
            sheet.column_dimensions[column].width = width

        book.save(self.output_file)
